/* Copyright © 2010 LPRM
Universidade Federal do Espírito Santo - RNP-GT CWTools

This file is part of JoinUs!.

JoinUs! is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

JoinUs! is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with JoinUs!. If not, see <http://www.gnu.org/licenses/>. */
package com.isisufes.joinus.appserver;

import java.lang.Integer;
import java.sql.*;

public class Campaign extends Servlet1
{
	/**
	 * 
	 */
	private static final long serialVersionUID = -162267514080702660L;

	private static String[] cfields = { "idCampaign", "name", "description",
		"area", "creation", "expiration", "idProfile", "publicCamp" };

	private static String[] afields = { "idAttachment", "filename", "size",
		"description", "mimetype", "hash" };

	@SuppressWarnings("unused")
	private PreparedStatement ps_near, ps_invp, ps_delp, ps_invc, ps_addi,
		ps_create, ps_create2, ps_insall, ps_join, ps_cgu, ps_csu, ps_upd,
		ps_joins, ps_sjoin, ps_own, ps_mem, ps_insp, ps_insc, ps_can,
		ps_idcamp, ps_mycmps, ps_cp, ps_cp2, psListCampaigns, psListCampaigners, psMembers;
	
	protected void initStmt()
	{
		// Lista todas as campanhas cadastradas no sistema.
		psListCampaigns = DBConn.stmt("SELECT * FROM campaign WHERE publicCamp=1");
		
		// Lista os usuários participantes de uma dada campanha.
		psListCampaigners = DBConn.stmt("SELECT login, firstname, lastname " +
				"FROM joinscampaign INNER JOIN profile " +
				"ON (joinscampaign.idcampaign=? AND joinscampaign.idprofile=profile.idprofile)");
		
		// Lista os usuários participantes de uma dada campanha com a posição deles.
		psMembers = DBConn.stmt("SELECT joinscampaign.idprofile AS id, firstname || ' ' || lastname AS name, gpslat || ':' || gpslng AS position " +
				"FROM joinscampaign INNER JOIN profile " +
				"ON (joinscampaign.idcampaign=? AND joinscampaign.idprofile=profile.idprofile)");
		
		ps_near = DBConn.stmt("select * from Campaign where point(?,?) @ area and publicCamp=1 and idCampaign not in (select idCampaign from SentInvites where idProfile=?)");

		ps_invp = DBConn.stmt("select c.idCampaign as idCampaign,name,description,area,creation,expiration,publicCamp,c.idProfile as idProfile, ip.inviter"
			+" from Campaign c inner join InvitesProfile ip on (c.idCampaign=ip.idCampaign)"
			+" where invited=?");

		ps_cp = DBConn.stmt("select c.idCampaign as idCampaign,name,description,area,creation,expiration,publicCamp,idProfile"
			+" from Campaign where idCampaign=?");

		ps_cp2 = DBConn.stmt("select firstName, lastName"
			+" from Profile p inner join JoinsCampaign jc on (p.idProfile = jc.idProfile)"
			+" where idCampaign=?");

		ps_delp = DBConn.stmt("delete from InvitesProfile where invited=?");

		ps_invc = DBConn.stmt("select c.idCampaign as idCampaign,name,description,area,creation,expiration,idProfile,idCommunity,publicCamp"
			+" from Campaign c inner join InvitesCommunity ic on (c.idCampaign=ic.idCampaign)"
			+" where idCommunity in"
			   +" (select idCommunity from JoinsCommunity where idProfile=?)"
			+" and point(?,?) @ area"
			+" and c.idCampaign not in (select idCampaign from SentInvites where idProfile=?)");

		ps_addi = DBConn.stmt("insert into SentInvites(idCampaign,idProfile) values(?::bigint,?::bigint)");

		ps_create = DBConn.stmt("insert into Campaign"+
			"(idProfile,name,description,area,expiration,idCampaignType,publicCamp)"+
			" values (?::bigint,?,?,circle(point(?,?),?),?::timestamp,1,?)");

		ps_idcamp = DBConn.stmt("select currval('campaign_idcampaign_seq') as idc");

		ps_create2 = DBConn.stmt("insert into JoinsCampaign(idCampaign,idProfile,lastVersionSent,joinSent)"
			+" values(currval('campaign_idcampaign_seq'), ?::bigint,0,0)");

		ps_insall = DBConn.stmt("insert into InvitesProfile (idCampaign,inviter,invited)"+
			" select currval('campaign_idcampaign_seq'),?::bigint,idProfile from Profile");

		ps_join = DBConn.stmt("insert into JoinsCampaign (idProfile,idCampaign,"
			+"lastVersionSent,joinSent) values (?::bigint,?::bigint,0,0)");

		ps_cgu = DBConn.stmt("select * from Attachment where idCampaign=? and version > ?");

		ps_csu = DBConn.stmt("update JoinsCampaign set lastVersionSent=? where idJoinsCampaign=?");

		ps_upd = DBConn.stmt("select idJoinsCampaign, c.idCampaign, lastVersionSent, version "+
			"from JoinsCampaign jc inner join Campaign c on (jc.idCampaign=c.idCampaign) "+
			"where jc.idProfile=? and version > lastVersionSent;");

		ps_joins = DBConn.stmt("select c.idCampaign,name,description,area,creation,expiration,c.idProfile,publicCamp"+
		" from JoinsCampaign jc inner join Campaign c"+
			" on (jc.idCampaign=c.idCampaign)"+
			" where jc.idProfile=? and joinSent=0");

		ps_sjoin = DBConn.stmt("update JoinsCampaign set joinSent=1 where idProfile=?");

		ps_own = DBConn.stmt("select idCampaign from Campaign where idProfile=? and idCampaign=?");

		ps_mem = DBConn.stmt("select idCampaign from JoinsCampaign where idProfile=? and idCampaign=?");

		ps_insp = DBConn.stmt("insert into InvitesProfile(idCampaign,inviter,invited)"
			+" values(?::bigint,?::bigint,?::bigint)");

		ps_insc = DBConn.stmt("insert into InvitesCommunity(idCampaign,idCommunity)"
			+" values(?::bigint,?::bigint)");

		ps_can = DBConn.stmt("select idCampaign from SentInvites where idProfile=? and idCampaign=?");

		ps_mycmps = DBConn.stmt("select c.idCampaign as idCampaign,name,description,area,creation,expiration,c.idProfile as idProfile"
			+" from Campaign c inner join JoinsCampaign jc on (jc.idCampaign=c.idCampaign) where jc.idProfile=?");
	}

	private String strCampaign(ResultSet rs) throws Exception
	{
		StringBuilder sb = new StringBuilder();

		sb.append("<Campaign>\n");
		for (String field: cfields) {
			String value = rs.getString(field);
			if (field.equals("area"))
				value = value.replaceAll("[<>]", "");
			sb.append("<" + field + ">" + value + "</" + field + ">\n");
		}
		sb.append("</Campaign>\n");

		return sb.toString();
	}

	private String strCampaigns(ResultSet rs) throws Exception
	{
		StringBuilder sb = new StringBuilder();

		while (rs.next()) {
			sb.append(strCampaign(rs));
		}

		rs.close();
		return sb.toString();
	}

	private boolean amOwner(String idCampaign) throws Exception
	{
		ps_own.clearParameters();
		ps_own.setInt(1, Integer.parseInt(id));
		ps_own.setInt(2, Integer.parseInt(idCampaign));
		ResultSet rs = ps_own.executeQuery();
		boolean ret = rs.next();
		rs.close();
		return ret;
	}

	private boolean amMember(String idCampaign) throws Exception
	{
		ps_mem.clearParameters();
		ps_mem.setInt(1, Integer.parseInt(id));
		ps_mem.setInt(2, Integer.parseInt(idCampaign));
		System.out.println("idcamp: " + idCampaign);
		ResultSet rs = ps_mem.executeQuery();
		boolean ret = rs.next();
		rs.close();
		return ret;
	}

	private String strAttachments(ResultSet rs) throws Exception
	{
		StringBuilder sb = new StringBuilder();

		while (rs.next()) {
			sb.append("<Attachment>\n");

			for (String field: afields)
				sb.append("<"+field+">"+rs.getString(field)+"</"+field+">\n");

			sb.append("</Attachment>\n");
		}

		rs.close();
		return sb.toString();
	}

	private void addInvite(String idCampaign) throws Exception
	{
		try {
			ps_addi.clearParameters();
			ps_addi.setInt(1, Integer.parseInt(idCampaign));
			ps_addi.setInt(2, Integer.parseInt(id));
			ps_addi.executeUpdate();
		}
		catch (SQLException e) { } // ignora chave duplicada
	}

	private String getCampaign() throws Exception
	{
		StringBuilder sb = new StringBuilder();

		//ps_cp.clearParameters();
		//ps_cp.setString(1, arg("idCampaign"));
		//sb.append(strCampaigns(ps_cp.executeQuery()));

		ps_cp2.clearParameters();
		ps_cp2.setInt(1, Integer.parseInt(arg("idCampaign")));
		ResultSet rs = ps_cp2.executeQuery();

		while (rs.next()) {
			sb.append(rs.getString("firstName"));
			sb.append(" ");
			sb.append(rs.getString("lastName"));
			sb.append(":");
		}

		return sb.toString();
	}

	private String getInvitations() throws Exception
	{
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();

		sb.append("<Invites>");

		ps_invc.clearParameters();
		ps_invc.setInt(1, Integer.parseInt(id));
		ps_invc.setDouble(2, argd("posX"));
		ps_invc.setDouble(3, argd("posY"));
		ps_invc.setInt(4, Integer.parseInt(id));
		rs = ps_invc.executeQuery();

		sb.append("<CommunityInvites>");
		while (rs.next()) {
			sb.append("<CommunityInvite>");
			sb.append("<idCommunity>"+rs.getString("idCommunity")+"</idCommunity>");
			sb.append(strCampaign(rs));
			sb.append("</CommunityInvite>");

			addInvite(rs.getString("idCampaign"));
		}
		sb.append("</CommunityInvites>");
		rs.close();

		ps_invp.clearParameters();
		ps_invp.setInt(1, Integer.parseInt(id));
		rs = ps_invp.executeQuery();

		sb.append("<PersonalInvites>");
		while (rs.next()) {
			sb.append("<PersonalInvite>");
			sb.append("<Inviter>"+rs.getString("inviter")+"</Inviter>");
			sb.append(strCampaign(rs));
			sb.append("</PersonalInvite>");

			addInvite(rs.getString("idCampaign"));
		}
		sb.append("</PersonalInvites>");
		rs.close();

		ps_delp.clearParameters();
		ps_delp.setInt(1, Integer.parseInt(id));
		ps_delp.executeUpdate();

		ps_near.clearParameters();
		ps_near.setDouble(1, argd("posX"));
		ps_near.setDouble(2, argd("posY"));
		ps_near.setInt(3, Integer.parseInt(id));
		rs = ps_near.executeQuery();

		sb.append("<PublicInvites>");
		while (rs.next()) {
			addInvite(rs.getString("idCampaign"));
		sb.append(strCampaign(rs));
	}
		sb.append("</PublicInvites>");

		rs.close();

		sb.append("</Invites>");

		return sb.toString();
	}

	private String createCampaign() throws Exception
	{
		ps_create.clearParameters();
		ps_create.setInt(1, Integer.parseInt(id));
		ps_create.setString(2, arg("name"));
		ps_create.setString(3, arg("description"));
		ps_create.setDouble(4, argd("posX"));
		ps_create.setDouble(5, argd("posY"));
		ps_create.setDouble(6, argd("radius"));
		ps_create.setString(7, arg("expiration"));
		ps_create.setInt(8, Integer.parseInt(arg("public")));
		ps_create.executeUpdate();

		ResultSet rs = ps_idcamp.executeQuery();
		rs.next();
		String idCampaign = rs.getString("idc");
		rs.close();

		ps_create2.clearParameters();
		ps_create2.setInt(1, Integer.parseInt(id));
		ps_create2.executeUpdate();

		return idCampaign;
	}

	private String inviteProfiles() throws Exception
	{
		if (!amOwner(arg("idCampaign"))) throw new Exception("only owners can invite");

		String[] list = (String[])Crypter.unserialize(Base64.decode(arg("list")));

		for (String pid: list) {
			ps_insp.clearParameters();
			ps_insp.setInt(1, Integer.parseInt(arg("idCampaign")));
			ps_insp.setInt(2, Integer.parseInt(id));
			ps_insp.setInt(3, Integer.parseInt(pid));
			ps_insp.executeUpdate();
		}

		return "";
	}

	private String inviteCommunities() throws Exception
	{
		if (!amOwner(arg("idCampaign"))) throw new Exception("only owners can invite");

		String[] list = (String[])Crypter.unserialize(Base64.decode(arg("list")));

		for (String cid: list) {
		System.out.println(cid);
			ps_insc.clearParameters();
			ps_insc.setInt(1, Integer.parseInt(arg("idCampaign")));
			ps_insc.setInt(2, Integer.parseInt(cid));
			ps_insc.executeUpdate();
		}

		return "";
	}

	private String getCampaignUpdates(String idCampaign, String lastVersion)
		throws Exception
	{
		ps_cgu.clearParameters();
		ps_cgu.setInt(1, Integer.parseInt(idCampaign));
		ps_cgu.setInt(2, Integer.parseInt(lastVersion));

		return "<Campaign idCampaign=\""+idCampaign+"\">\n"
			+strAttachments(ps_cgu.executeQuery())+"</Campaign>\n";
	}

	private void setCampaignUpdated(String idJoinsCampaign, String version)
		throws Exception
	{
		ps_csu.clearParameters();
		ps_csu.setInt(1, Integer.parseInt(version));
		ps_csu.setInt(2, Integer.parseInt(idJoinsCampaign));
		ps_csu.executeUpdate();
	}

	private String getUpdates() throws Exception
	{
		ps_upd.clearParameters();
		ps_upd.setInt(1, Integer.parseInt(id));

		ResultSet rs = ps_upd.executeQuery();

		StringBuilder sb = new StringBuilder();
		sb.append("<Updates>\n");

		sb.append("<Attachments>\n");
		while (rs.next()) {
			String idJoinsCampaign = rs.getString("idJoinsCampaign");
			String idCampaign = rs.getString("idCampaign");
			String version = rs.getString("version");
			String lastVersion = rs.getString("lastVersionSent");

			sb.append(getCampaignUpdates(idCampaign, lastVersion));
			setCampaignUpdated(idJoinsCampaign, version);
		}
		sb.append("</Attachments>\n");
		rs.close();

		ps_joins.clearParameters();
		ps_joins.setInt(1, Integer.parseInt(id));

		sb.append("<Joins>\n");
		sb.append(strCampaigns(ps_joins.executeQuery()));
		sb.append("</Joins>\n");

		ps_sjoin.clearParameters();
		ps_sjoin.setInt(1, Integer.parseInt(id));
		ps_sjoin.executeUpdate();

		sb.append("</Updates>\n");

		return sb.toString();
	}

	private String getAttachments() throws Exception
	{
		if (!amMember(arg("idCampaign"))) throw new Exception("you are not a member");

		ps_cgu.clearParameters();
		ps_cgu.setInt(1, Integer.parseInt(arg("idCampaign")));
		ps_cgu.setInt(2, 0);

		return "<Attachments>"+strAttachments(ps_cgu.executeQuery())+"</Attachments>";
	}

	private boolean wasInvited(String idCampaign) throws Exception
	{
		ps_can.clearParameters();
		ps_can.setInt(1, Integer.parseInt(id));
		ps_can.setInt(2, Integer.parseInt(arg("idCampaign")));
		ResultSet rs = ps_can.executeQuery();
		boolean ret = rs.next();
		rs.close();

		return ret;
	}

	private String joinCampaign() throws Exception
	{
		if (!wasInvited(arg("idCampaign"))) throw new Exception("not invited");

		ps_join.clearParameters();
		ps_join.setInt(1, Integer.parseInt(id));
		ps_join.setInt(2, Integer.parseInt(arg("idCampaign")));
		ps_join.executeUpdate();

		return "";
	}

	private String getCampaigns() throws Exception
	{
		ps_mycmps.clearParameters();
		ps_mycmps.setInt(1, Integer.parseInt(id));

		return "<Campaigns>"+strCampaigns(ps_mycmps.executeQuery())+"</Campaigns>";
	}

	protected String dispatch(String action)
		throws Exception
	{
		String ret = null;

		if (action.equals("getCampaign")) ret = getCampaign();
		if (action.equals("getInvitations")) ret = getInvitations();
		if (action.equals("inviteProfiles")) ret = inviteProfiles();
		if (action.equals("inviteCommunities")) ret = inviteCommunities();
		if (action.equals("createCampaign")) ret = createCampaign();
		if (action.equals("getUpdates")) ret = getUpdates();
		if (action.equals("getAttachments")) ret = getAttachments();
		if (action.equals("joinCampaign")) ret = joinCampaign();
		if (action.equals("getCampaigns")) ret = getCampaigns();
		if (action.equals("getMembers")) ret = getMembers();

		if (action.equals("list")) ret = listCampaigns();
		if (action.equals("users")) ret = listCampaigners();
		
		return ret;
	}
	
	private String listCampaigns() throws Exception {
		StringBuilder sb = new StringBuilder();
		sb.append("<Campaigns>\n");
		ResultSet rs = psListCampaigns.executeQuery();
		while (rs.next()) {
			sb.append(strCampaign(rs));
		}
		sb.append("</Campaigns>");
		return sb.toString();
	}
	
	private String listCampaigners() throws Exception {
		StringBuilder sb = new StringBuilder();
		sb.append("<Campaigners>\n");
		psListCampaigners.clearParameters();
		psListCampaigners.setInt(1, Integer.parseInt(arg("idCampaign")));
		ResultSet rs = psListCampaigners.executeQuery();
		String[] userFields = { "login", "firstname", "lastname" };
		while (rs.next()) {
			sb.append("<Campaigner>\n");
			for (String field : userFields) {
				sb.append("<" + field + ">" + rs.getString(field) + "</" + field + ">\n");
			}
			sb.append("</Campaigner>\n");
		}
		sb.append("</Campaigners>");
		return sb.toString();
	}
	
	private String generateXmlPart(ResultSet rs, String element, String[] fields) throws Exception{
		StringBuilder sb = new StringBuilder();
		while (rs.next()) {
			sb.append("<" + element + ">\n");
			for (String field : fields) {
				sb.append("\t<" + field + ">" + rs.getString(field) + "</" + field + ">\n");
			}
			sb.append("</" + element + ">\n");
		}
		return sb.toString();
	}

	private String getMembers() throws Exception {
		StringBuilder sb = new StringBuilder();
		sb.append("<Members>\n");
		psMembers.clearParameters();
		psMembers.setInt(1, Integer.parseInt(arg("idCampaign")));
		ResultSet rs = psMembers.executeQuery();
		String[] userFields = { "id", "name", "position" };
		sb.append(generateXmlPart(rs, "Member", userFields));
		sb.append("</Members>\n");
		return sb.toString();
	}
}
